﻿/*
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.Globalization;
using System.Data;

namespace IvyOrm
{
    /// <summary>
    /// Provides ExecuteNonQuery, ExecuteReader and ExecuteScalar extensions to the DbConnection and DbTransaction object.
    /// </summary>
    public static class ExecuteCommandExtension
    {
        #region ExecuteNonQuery
        /// <summary>
        /// Executes a SQL statement against a connection object.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>The number of rows affected.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static int ExecuteNonQuery(this DbConnection connection, string commandText, params object[] args)
        {
            return ExecuteNonQuery(connection, new IvyOrmOptions(), commandText, args);
        }
        /// <summary>
        /// Executes a SQL statement against a connection object.
        /// </summary>
        /// <param name="transaction">The transaction to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>The number of rows affected.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static int ExecuteNonQuery(this DbTransaction transaction, string commandText, params object[] args)
        {
            return ExecuteNonQuery(transaction, new IvyOrmOptions(), commandText, args);
        }

        /// <summary>
        /// Executes a SQL statement against a connection object.
        /// </summary>
        /// <param name="transaction">The transaction to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>The number of rows affected.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static int ExecuteNonQuery(this DbTransaction transaction, IvyOrmOptions options, string commandText, params object[] args)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction.Connection == null)
                throw new ArgumentException("must have the connection property set", "transaction");


            options = IvyOrmOptions.Defaults(transaction.Connection, options);

            var connection = transaction.Connection;

            using (var command = connection.CreateCommand())
            using (var commandBuilder = connection.GetFactory().CreateCommandBuilder())
            {
                command.Transaction = transaction;
                prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, System.Diagnostics.TraceEventType.Information, 0, "ExecuteNonQuery with the following command:\n" + command.CommandText);

                return command.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// Executes a SQL statement against a connection object.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>The number of rows affected.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static int ExecuteNonQuery(this DbConnection connection, IvyOrmOptions options, string commandText, params object[] args)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            options = IvyOrmOptions.Defaults(connection, options);

            using (var command = connection.CreateCommand())
            using (var commandBuilder = connection.GetFactory().CreateCommandBuilder())
            {
                prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, System.Diagnostics.TraceEventType.Information, 0, "ExecuteNonQuery with the following command:\n" + command.CommandText);

                return command.ExecuteNonQuery();
            }
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// Executes a SQL statement against a connection object and returns a reader.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="behavior">One of the System.Data.CommandBehavior values.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>A System.Data.Common.DbDataReader object.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static DbDataReader ExecuteReader(this DbConnection connection, CommandBehavior behavior, string commandText, params object[] args)
        {
            return ExecuteReader(connection, new IvyOrmOptions(), behavior, commandText, args);
        }
        /// <summary>
        /// Executes a SQL statement against a connection object and returns a reader.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>A System.Data.Common.DbDataReader object.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static DbDataReader ExecuteReader(this DbConnection connection, string commandText, params object[] args)
        {
            return ExecuteReader(connection, new IvyOrmOptions(), CommandBehavior.Default, commandText, args);
        }

        /// <summary>
        /// Executes a SQL statement against a connection object and returns a reader.
        /// </summary>
        /// <param name="transaction">The transaction to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>A System.Data.Common.DbDataReader object.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static DbDataReader ExecuteReader(this DbTransaction transaction, string commandText, params object[] args)
        {
            return ExecuteReader(transaction, new IvyOrmOptions(), CommandBehavior.Default, commandText, args);
        }
        /// <summary>
        /// Executes a SQL statement against a connection object and returns a reader.
        /// </summary>
        /// <param name="transaction">The transaction to execute the command on.</param>
        /// <param name="behavior">One of the System.Data.CommandBehavior values.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>A System.Data.Common.DbDataReader object.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static DbDataReader ExecuteReader(this DbTransaction transaction, CommandBehavior behavior, string commandText, params object[] args)
        {
            return ExecuteReader(transaction, new IvyOrmOptions(), behavior, commandText, args);
        }

        /// <summary>
        /// Executes a SQL statement against a connection object and returns a reader.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="behavior">One of the System.Data.CommandBehavior values.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>A System.Data.Common.DbDataReader object.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static DbDataReader ExecuteReader(this DbConnection connection, IvyOrmOptions options, CommandBehavior behavior, string commandText, params object[] args)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            //Firebird disposes the reader when the command is disposed making this method not work.
            if (connection.GetType().Name == KnownConnectionTypes.FbConnection)
                throw new NotSupportedException("Execute Reader is not supported for Firebird Connections. (02CB7395)");

            options = IvyOrmOptions.Defaults(connection, options);

            using (var command = connection.CreateCommand())
            using (var commandBuilder = connection.GetFactory().CreateCommandBuilder())
            {
                prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, System.Diagnostics.TraceEventType.Information, 0, "ExecuteReader with the following command:\n" + command.CommandText);

                var reader = command.ExecuteReader(behavior);
                return reader;
            }
        }
        /// <summary>
        /// Executes a SQL statement against a connection object and returns a reader.
        /// </summary>
        /// <param name="transaction">The transaction to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="behavior">One of the System.Data.CommandBehavior values.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>A System.Data.Common.DbDataReader object.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static DbDataReader ExecuteReader(this DbTransaction transaction, IvyOrmOptions options, CommandBehavior behavior, string commandText, params object[] args)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction.Connection == null)
                throw new ArgumentException("must have the connection property set", "transaction");

            var connection = transaction.Connection;
            options = IvyOrmOptions.Defaults(connection, options);

            //Firebird disposes the reader when the command is disposed making this method not work.
            if (connection.GetType().Name == KnownConnectionTypes.FbConnection)
                throw new NotSupportedException("Execute Reader is not supported for Firebird Connections. (02CB7395)");

            using (var command = connection.CreateCommand())
            using (var commandBuilder = connection.GetFactory().CreateCommandBuilder())
            {
                command.Transaction = transaction;
                prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, System.Diagnostics.TraceEventType.Information, 0, "ExecuteReader with the following command:\n" + command.CommandText);

                return command.ExecuteReader(behavior);
            }
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// Executes a SQL statement against a connection object.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>The first column of the first row in the result set.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static object ExecuteScalar(this DbConnection connection, string commandText, params object[] args)
        {
            return ExecuteScalar(connection, new IvyOrmOptions(), commandText, args);
        }
        /// <summary>
        /// Executes a SQL statement against a connection object.
        /// </summary>
        /// <param name="transaction">The transaction to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>The first column of the first row in the result set.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static object ExecuteScalar(this DbTransaction transaction, string commandText, params object[] args)
        {
            return ExecuteScalar(transaction, new IvyOrmOptions(), commandText, args);
        }


        /// <summary>
        /// Executes a SQL statement against a connection object.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>The first column of the first row in the result set.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static object ExecuteScalar(this DbConnection connection, IvyOrmOptions options,  string commandText, params object[] args)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            options = IvyOrmOptions.Defaults(connection, options);

            using (var command = connection.CreateCommand())
            using (var commandBuilder = connection.GetFactory().CreateCommandBuilder())
            {
                prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, System.Diagnostics.TraceEventType.Information, 0, "ExecuteScalar with the following command:\n" + command.CommandText);

                return command.ExecuteScalar();
            }
        }
        /// <summary>
        /// Executes a SQL statement against a connection object.
        /// </summary>
        /// <param name="transaction">The transaction to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>The first column of the first row in the result set.</returns>
        /// <exception cref="System.Data.Common.DbException" />
        /// <exception cref="System.ArgumentNullException" />
        /// <exception cref="System.FormatException" />
        public static object ExecuteScalar(this DbTransaction transaction,IvyOrmOptions options,  string commandText, params object[] args)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction.Connection == null)
                throw new ArgumentException("must have the connection property set", "transaction");
            var connection = transaction.Connection;

            options = IvyOrmOptions.Defaults(connection, options);

            using (var command = connection.CreateCommand())
            using (var commandBuilder = connection.GetFactory().CreateCommandBuilder())
            {
                command.Transaction = transaction;
                prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, System.Diagnostics.TraceEventType.Information, 0, "ExecuteScalar with the following command:\n" + command.CommandText);

                return command.ExecuteScalar();
            }
        }
        #endregion

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        internal static void prepairCommand(DbCommand command, DbCommandBuilder commandBuilder, string commandText, object[] args)
        {
            //@@ ==> @
            //@0 ==> GetParameterPlaceholder(0)
            
            int maxVar = -1;
            string newCommandText = "";

            var preEscaped = Regex.Split(commandText, @"\@\@");
            for (int i = 0; i < preEscaped.Length; i++)
            {
                var commandPiece = preEscaped[i];
                if (i != 0)
                    newCommandText += "@";

                newCommandText += Regex.Replace(commandPiece, @"\@([0-9]*)", (m) =>
                {
                    if (string.IsNullOrEmpty(m.Groups[1].Value))
                        throw new FormatException("Failed to prepare command, variable was not in a correct format. To escape '@' use '@@' otherwise format variables @0, @1, ... ");

                    var placeholderIndex = int.Parse(m.Groups[1].Value, CultureInfo.InvariantCulture);

                    maxVar = Math.Max(placeholderIndex, maxVar);
                    return commandBuilder.GetParameterPlaceholder(placeholderIndex);
                    
                });
            }

            List<DbParameter> dbParameters = new List<DbParameter>();
            if (args != null)
            {
                if (args.Length < maxVar + 1)
                {
                    var message = string.Format(CultureInfo.InvariantCulture, "Failed to prepare command, expecting {0} arguments but only received {1}", (maxVar + 1), args.Length);
                    throw new FormatException(message);
                }

                for (int i = 0; i < args.Length; i++)
                {
                    var parameter = command.CreateParameter();
                    parameter.Value = args[i] == null ? DBNull.Value : args[i];
                    parameter.ParameterName = commandBuilder.GetParameterPlaceholder(i);
                    dbParameters.Add(parameter);
                }
            }

            command.Parameters.Clear();
            command.CommandText = newCommandText;
            foreach (var dbParameter in dbParameters)
                command.Parameters.Add(dbParameter);
        }
    }
}
